﻿CREATE TABLE [dbo].[AW_Lelong] (
    [LelongId]      UNIQUEIDENTIFIER CONSTRAINT [DF_AW_Lelong_LelongId] DEFAULT (newid()) NOT NULL,
    [LelongTipCode] NVARCHAR (10)    NULL,
    [UnitNo]        NVARCHAR (50)    NULL,
    [Address]       NVARCHAR (300)   NULL,
    [Price]         DECIMAL (18, 2)  NULL,
    [Type]          NVARCHAR (50)    NULL,
    [LandArea]      FLOAT (53)       NULL,
    [AuctionDate]   DATE             NULL,
    [AuctionTime]   TIME (7)         NULL,
    [AuctionVenue]  NVARCHAR (300)   NULL,
    [Bank]          NVARCHAR (50)    NULL,
    [Auctioneer]    NVARCHAR (300)   NULL,
    [Lawyer]        NVARCHAR (300)   NULL,
    [Description]   NVARCHAR (MAX)   NULL,
    [AgentID]       UNIQUEIDENTIFIER NULL,
    [RemindDate]    DATE             NULL,
    [AgentRemark]   TEXT             NULL,
    [CreateDate]    DATETIME         CONSTRAINT [DF_AW_Lelong_CreateDate] DEFAULT (getdate()) NULL,
    [CreateWho]     UNIQUEIDENTIFIER NULL,
    [UpdateDate]    DATETIME         NULL,
    [UpdateWho]     UNIQUEIDENTIFIER NULL,
    [StatusDelete]  TINYINT          CONSTRAINT [DF_AW_Lelong_IsDeleted] DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_AW_Lelong] PRIMARY KEY CLUSTERED ([LelongId] ASC)
);




GO
CREATE TRIGGER [dbo].[AW_Lelong_dss_update_trigger] ON [dbo].[AW_Lelong] FOR UPDATE AS
SET NOCOUNT ON
DECLARE @marker_create_scope_local_id INT
DECLARE @marker_scope_create_peer_timestamp BIGINT
DECLARE @marker_scope_create_peer_key INT
DECLARE @marker_local_create_peer_timestamp BIGINT
DECLARE @marker_local_create_peer_key INT
DECLARE @marker_state INT
SELECT TOP 1 @marker_create_scope_local_id = [provision_scope_local_id], @marker_local_create_peer_timestamp = [provision_timestamp], @marker_local_create_peer_key = [provision_local_peer_key], @marker_scope_create_peer_timestamp = [provision_scope_peer_timestamp], @marker_scope_create_peer_key = [provision_scope_peer_key], @marker_state = [state]
FROM [DataSync].[provision_marker_dss]
WHERE [object_id] = 857978333 
 AND [owner_scope_local_id] = 0

MERGE [DataSync].[AW_Lelong_dss_tracking] AS [target] 
USING (SELECT [i].[LelongId] FROM INSERTED AS [i]) AS source([LelongId]) 
ON ([target].[LelongId] = [source].[LelongId])
WHEN MATCHED THEN
UPDATE SET [sync_row_is_tombstone] = 0, 
[local_update_peer_key] = 0, 
[update_scope_local_id] = NULL, [last_change_datetime] = GETDATE()
WHEN NOT MATCHED THEN
INSERT (
[LelongId] ,
[create_scope_local_id], [scope_create_peer_key], [scope_create_peer_timestamp], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone], [last_change_datetime]) 
VALUES (
[source].[LelongId],@marker_create_scope_local_id, @marker_scope_create_peer_key, @marker_scope_create_peer_timestamp, 0, @marker_local_create_peer_timestamp , NULL, 0, 0, GETDATE() );
GO
CREATE TRIGGER [dbo].[AW_Lelong_dss_insert_trigger] ON [dbo].[AW_Lelong] FOR INSERT AS
SET NOCOUNT ON
DECLARE @marker_create_scope_local_id INT
DECLARE @marker_scope_create_peer_timestamp BIGINT
DECLARE @marker_scope_create_peer_key INT
DECLARE @marker_local_create_peer_timestamp BIGINT
DECLARE @marker_local_create_peer_key INT
DECLARE @marker_state INT
SELECT TOP 1 @marker_create_scope_local_id = [provision_scope_local_id], @marker_local_create_peer_timestamp = [provision_timestamp], @marker_local_create_peer_key = [provision_local_peer_key], @marker_scope_create_peer_timestamp = [provision_scope_peer_timestamp], @marker_scope_create_peer_key = [provision_scope_peer_key], @marker_state = [state]
FROM [DataSync].[provision_marker_dss]
WHERE [object_id] = 857978333 
 AND [owner_scope_local_id] = 0

MERGE [DataSync].[AW_Lelong_dss_tracking] AS [target] 
USING (SELECT [i].[LelongId] FROM INSERTED AS [i]) AS source([LelongId]) 
ON ([target].[LelongId] = [source].[LelongId])
WHEN MATCHED THEN
UPDATE SET [sync_row_is_tombstone] = 0, 
[local_update_peer_key] = 0, 
[update_scope_local_id] = NULL, [last_change_datetime] = GETDATE()
WHEN NOT MATCHED THEN
INSERT (
[LelongId] ,
[create_scope_local_id], [scope_create_peer_key], [scope_create_peer_timestamp], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone], [last_change_datetime]) 
VALUES (
[source].[LelongId],NULL, NULL, NULL, 0, CAST(@@DBTS AS BIGINT) + 1, NULL, 0, 0, GETDATE() );
GO
CREATE TRIGGER [dbo].[AW_Lelong_dss_delete_trigger] ON [dbo].[AW_Lelong] FOR DELETE AS
SET NOCOUNT ON
DECLARE @marker_create_scope_local_id INT
DECLARE @marker_scope_create_peer_timestamp BIGINT
DECLARE @marker_scope_create_peer_key INT
DECLARE @marker_local_create_peer_timestamp BIGINT
DECLARE @marker_local_create_peer_key INT
DECLARE @marker_state INT
SELECT TOP 1 @marker_create_scope_local_id = [provision_scope_local_id], @marker_local_create_peer_timestamp = [provision_timestamp], @marker_local_create_peer_key = [provision_local_peer_key], @marker_scope_create_peer_timestamp = [provision_scope_peer_timestamp], @marker_scope_create_peer_key = [provision_scope_peer_key], @marker_state = [state]
FROM [DataSync].[provision_marker_dss]
WHERE [object_id] = 857978333 
 AND [owner_scope_local_id] = 0

MERGE [DataSync].[AW_Lelong_dss_tracking] AS [target] 
USING (SELECT [i].[LelongId] FROM DELETED AS [i]) AS source([LelongId]) 
ON ([target].[LelongId] = [source].[LelongId])
WHEN MATCHED THEN
UPDATE SET [sync_row_is_tombstone] = 1, 
[local_update_peer_key] = 0, 
[update_scope_local_id] = NULL, [last_change_datetime] = GETDATE()
WHEN NOT MATCHED THEN
INSERT (
[LelongId] ,
[create_scope_local_id], [scope_create_peer_key], [scope_create_peer_timestamp], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone], [last_change_datetime]) 
VALUES (
[source].[LelongId],@marker_create_scope_local_id, @marker_scope_create_peer_key, @marker_scope_create_peer_timestamp, 0, @marker_local_create_peer_timestamp , NULL, 0, 1, GETDATE() );